//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// Copyright 2007 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   MASTER_DEPARTMENT
// AUTHOR           :   Ray Guo 
// DATE CREATE      :   2007/07/11
// DESCRIPTION      :   a COMMON DAO file for the function, MASTER_DEPARTMENT
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2007/07/11   Ray Guo         1.0                 source is generated.
//
//
//
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

package com.erp.app.setup.dept.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.util.ProjectCommonUtil;
import com.erp.app.setup.dept.bizLogic.DeptCommonUtil;
import com.erp.app.setup.dept.criteria.DeptSrchCriteria;
import com.erp.app.setup.dept.vo.DeptHdrVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.vo.SimpleBaseVo;

/**
* <p>A <strong>CommonDao</strong> provides database access functions
*  for delegate classes.
*
* @author          Ray Guo 
* @version 1.0     2007/07/11
* @since           FrameWork 1.1
*
*/
public class DeptCommonDao {

	private DeptCommonUtil util = new DeptCommonUtil();

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Ray Guo 
	* @since      Framework 1.1
	*/
	public static String getHdrSqlQuery(SimpleBaseSrchCriteria obj) {

		DeptSrchCriteria criteria = (DeptSrchCriteria) obj;
		String sql = "";
		String whereClause = hdrToWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY COMPANY_CODE, DEPT_CODE";

		if(!criteria.isSearchResult()){
			sql = "SELECT COMPANY_CODE, DEPT_CODE, DEPT_NAME, STATUS, GROUP_CODE, "
			+ "BRAND_CODE, CUST_CODE, DIVISION_CODE, OUTLET, IS_US, "
			+ "IS_MERCHANDISING_DEPARTMENT, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
			+ "FROM T_DEPT_HDR "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		} else {
			sql = "SELECT COMPANY_CODE, DEPT_CODE, DEPT_NAME, STATUS, GROUP_CODE, "
			+ "BRAND_CODE, CUST_CODE, DIVISION_CODE, OUTLET, IS_US, "
			+ "IS_MERCHANDISING_DEPARTMENT, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
			+ "FROM T_DEPT_HDR "
			+ whereClause
			+ groupByClause
			+ orderByClause;
		}

		return sql;
	}

	/**
	* Sets the values of a result set to a vo for a search screen
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Ray Guo 
	* @since      Framework 1.1
	*/
	public static SimpleBaseVo getHdrSearchResultSetValues(ResultSet rs) throws SQLException {

//		throw new RuntimeException("Please get the ResultSet values for the search screen");
		DeptHdrVo vo = new DeptHdrVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setDeptCode(rs.getString("DEPT_CODE"));
		vo.setHdrDeptName(rs.getString("DEPT_NAME"));
		vo.setHdrStatus(rs.getString("STATUS"));
		vo.setHdrGroupCode(rs.getString("GROUP_CODE"));
		vo.setHdrBrandCode(rs.getString("BRAND_CODE"));
		vo.setHdrCustCode(rs.getString("CUST_CODE"));
		vo.setHdrDivisionCode(rs.getString("DIVISION_CODE"));
		vo.setHdrOutlet(rs.getString("OUTLET"));
		vo.setHdrIsUs(rs.getString("IS_US"));
		vo.setHdrIsMerchandisingDepartment(rs.getString("IS_MERCHANDISING_DEPARTMENT"));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));

		return vo;
	

	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Ray Guo 
	* @since      Framework 1.1
	*/
	public static String hdrToWhereClause(DeptSrchCriteria crit) {

		DeptSrchCriteria criteria = (DeptSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		ProjectCommonUtil.addHdrEquals(whereClause,"COMPANY_CODE",criteria.getSrchCompanyCode());
		ProjectCommonUtil.addHdrBetween(whereClause,"DEPT_CODE",criteria.getSrchDeptCodeFr(),criteria.getSrchDeptCodeTo());
		ProjectCommonUtil.addHdrLike(whereClause,"DEPT_NAME",criteria.getSrchHdrDeptNameFr());
		ProjectCommonUtil.addHdrEquals(whereClause,"STATUS",criteria.getSrchHdrStatus());
		
//		if (criteria.getSrchCompanyCode() != null) {
//			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
//			whereClause.append(" COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
//		}
//
//		if (criteria.getSrchDeptCodeFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("DEPT_CODE >= '"
//				+ criteria.getSrchDeptCodeFr().toUpperCase().trim() + "' ");
//		}
//		if (criteria.getSrchDeptCodeTo() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("DEPT_CODE >= '"
//				+ criteria.getSrchDeptCodeTo().toUpperCase().trim() + "' ");
//		}
//
//		if (criteria.getSrchHdrDeptNameFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("DEPT_NAME LIKE '%"
//				+ criteria.getSrchHdrDeptNameFr().toUpperCase().trim() + "%' ");
//		}
//		
//		if (criteria.getSrchHdrGroupCodeFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("GROUP_CODE = '"
//				+ criteria.getSrchHdrGroupCodeFr().toUpperCase().trim() + "' ");
//		}
//
//		if (criteria.getSrchHdrBrandCodeFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("BRAND_CODE = '"
//				+ criteria.getSrchHdrBrandCodeFr().toUpperCase().trim() + "' ");
//		}
//		
//		if (criteria.getSrchHdrStatus() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("STATUS = '"
//				+ criteria.getSrchHdrStatus().toUpperCase().trim() + "' ");
//		}
//
//		if (criteria.getSrchHdrIsUsFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("IS_US = '"
//				+ criteria.getSrchHdrIsUsFr().toUpperCase().trim() + "' ");
//		}
//
//		if (criteria.getSrchHdrIsMerchandisingDepartmentFr() != null) {
//			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
//			whereClause.append("IS_MERCHANDISING_DEPARTMENT = '"
//				+ criteria.getSrchHdrIsMerchandisingDepartmentFr().toUpperCase().trim() + "' ");
//		}

		/*
		if (criteria.getSrchProgram() != null) {
			whereClause.append(whereClause.length() == 0 ? " WHERE " : " AND ");
			whereClause.append(" SALES_CONTRACT_NO IN ");
			whereClause.append(" (SELECT SALES_CONTRACT_NO FROM T_SALES_CONTRACT_DTL");
			if (criteria.getSrchProgram() != null) {
				whereClause.append(" WHERE PROGRAM ='" + criteria.getSrchProgram().toUpperCase().trim() + "'");
			}
			whereClause.append(")");
		}
		if (criteria.getSrchSalesContractNoFr() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("SALES_CONTRACT_NO >= '"
				+ criteria.getSrchSalesContractNoFr().toUpperCase().trim() + "' ");
		}
		if (criteria.getSrchSalesContractNoTo() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("SALES_CONTRACT_NO <= '"
				+ criteria.getSrchSalesContractNoTo().toUpperCase().trim() + "' ");
		}
		if (criteria.getSrchPoDateFr() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" PO_DATE >= TO_DATE('"
				+ criteria.getSrchPoDateFr() + "', '"
				+ ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
		}
		if (criteria.getSrchPoDateTo() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append(" PO_DATE <= TO_DATE('"
				+ criteria.getSrchPoDateTo() + "', '"
				+ ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
		}
		*/

		return whereClause.toString();
	}

}
